"""
XML Configuration Manager for GCR Solver Manager

Provides database management for XML configurations with metadata tracking,
search capabilities, and direct execution support.
"""

import json
import sqlite3
import logging
from pathlib import Path
from typing import Dict, List, Optional, Any, Tuple
from datetime import datetime
import hashlib
import xml.etree.ElementTree as ET

from .xml_config import XMLConfigParser, ParameterList

logger = logging.getLogger(__name__)


class XMLConfigDatabase:
    """Database manager for XML configurations."""
    
    def __init__(self, db_path: Optional[str] = None):
        """Initialize XML config database."""
        if db_path is None:
            # Default to GSM cache directory
            cache_dir = Path.home() / '.cache' / 'gsm'
            cache_dir.mkdir(parents=True, exist_ok=True)
            db_path = cache_dir / 'xml_configs.db'
        
        self.db_path = str(db_path)
        self._init_database()
    
    def _init_database(self):
        """Initialize database schema."""
        try:
            with sqlite3.connect(self.db_path) as conn:
                conn.execute('''
                    CREATE TABLE IF NOT EXISTS xml_configs (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        name TEXT NOT NULL,
                        description TEXT,
                        file_path TEXT NOT NULL,
                        work_dir TEXT,
                        binary_path TEXT,
                        mpi_processes INTEGER,
                        log_file TEXT,
                        config_hash TEXT UNIQUE,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        last_used TIMESTAMP,
                        use_count INTEGER DEFAULT 0,
                        metadata TEXT,
                        UNIQUE(file_path)
                    )
                ''')
                
                # Create index for faster searches
                conn.execute('CREATE INDEX IF NOT EXISTS idx_name ON xml_configs(name)')
                conn.execute('CREATE INDEX IF NOT EXISTS idx_config_hash ON xml_configs(config_hash)')
                conn.execute('CREATE INDEX IF NOT EXISTS idx_last_used ON xml_configs(last_used)')
                
            logger.debug(f"XML config database initialized: {self.db_path}")
            
        except Exception as e:
            logger.error(f"Failed to initialize XML config database: {e}")
            raise
    
    def add_xml_config(self, file_path: str, name: Optional[str] = None, 
                      description: Optional[str] = None) -> int:
        """Add XML configuration to database."""
        try:
            file_path = str(Path(file_path).resolve())
            
            if not Path(file_path).exists():
                raise FileNotFoundError(f"XML config file not found: {file_path}")
            
            # Parse XML to extract metadata
            parser = XMLConfigParser()
            if not parser.load_from_file(file_path):
                raise ValueError(f"Failed to parse XML: {parser.get_last_error()}")
            
            config = parser.parse()
            
            # Extract key parameters
            binary_section = config.get_sublist("Binary") if config.has_sublist("Binary") else None
            logging_section = config.get_sublist("Logging") if config.has_sublist("Logging") else None
            
            binary_path = binary_section.get_string("binary_path") if binary_section else None
            work_dir = binary_section.get_string("work_dir") if binary_section else None
            mpi_processes = binary_section.get_int("mpi_processes", 1) if binary_section else 1
            log_file = logging_section.get_string("log_file") if logging_section else None
            
            # Generate config hash for deduplication
            with open(file_path, 'rb') as f:
                config_hash = hashlib.md5(f.read()).hexdigest()
            
            # Use filename as default name
            if name is None:
                name = Path(file_path).stem
            
            # Extract description from XML if not provided
            if description is None:
                description = f"Config for {binary_path or 'binary'}"
                if config.name and config.name != "Root":
                    description = f"{config.name} configuration"
            
            # Create metadata
            metadata = {
                'xml_name': config.name,
                'sections': list(config.get_sublist_names()),
                'parameters': self._extract_all_parameters(config),
                'file_size': Path(file_path).stat().st_size,
                'file_mtime': datetime.fromtimestamp(Path(file_path).stat().st_mtime).isoformat()
            }
            
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.execute('''
                    INSERT OR REPLACE INTO xml_configs 
                    (name, description, file_path, work_dir, binary_path, mpi_processes, 
                     log_file, config_hash, metadata)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                ''', (
                    name, description, file_path, work_dir, binary_path, mpi_processes,
                    log_file, config_hash, json.dumps(metadata)
                ))
                
                config_id = cursor.lastrowid
                logger.info(f"Added XML config '{name}' (ID: {config_id})")
                return config_id
                
        except Exception as e:
            logger.error(f"Failed to add XML config: {e}")
            raise
    
    def _extract_all_parameters(self, param_list: ParameterList) -> Dict[str, Any]:
        """Extract all parameters from ParameterList recursively."""
        result = {}
        
        # Add direct parameters
        for name in param_list.get_parameter_names():
            param = param_list.get_parameter(name)
            result[name] = {
                'type': param.type,
                'value': param.value
            }
        
        # Add sublist parameters
        for sublist_name in param_list.get_sublist_names():
            sublist = param_list.get_sublist(sublist_name)
            result[sublist_name] = self._extract_all_parameters(sublist)
        
        return result
    
    def list_configs(self, limit: int = 20, search: Optional[str] = None) -> List[Dict[str, Any]]:
        """List XML configurations with optional search."""
        try:
            query = '''
                SELECT id, name, description, file_path, work_dir, binary_path, 
                       mpi_processes, log_file, created_at, last_used, use_count
                FROM xml_configs
            '''
            params = []
            
            if search:
                query += ' WHERE name LIKE ? OR description LIKE ? OR file_path LIKE ?'
                search_pattern = f'%{search}%'
                params.extend([search_pattern, search_pattern, search_pattern])
            
            query += ' ORDER BY last_used DESC NULLS LAST, created_at DESC LIMIT ?'
            params.append(limit)
            
            with sqlite3.connect(self.db_path) as conn:
                conn.row_factory = sqlite3.Row
                cursor = conn.execute(query, params)
                
                results = []
                for row in cursor.fetchall():
                    config_info = dict(row)
                    # Convert timestamps
                    for field in ['created_at', 'last_used']:
                        if config_info[field]:
                            try:
                                dt = datetime.fromisoformat(config_info[field].replace('Z', '+00:00'))
                                config_info[field] = dt.strftime('%Y-%m-%d %H:%M')
                            except:
                                pass
                    results.append(config_info)
                
                return results
                
        except Exception as e:
            logger.error(f"Failed to list XML configs: {e}")
            return []
    
    def get_config_by_id(self, config_id: int) -> Optional[Dict[str, Any]]:
        """Get XML configuration by ID."""
        try:
            with sqlite3.connect(self.db_path) as conn:
                conn.row_factory = sqlite3.Row
                cursor = conn.execute('''
                    SELECT * FROM xml_configs WHERE id = ?
                ''', (config_id,))
                
                row = cursor.fetchone()
                if row:
                    config = dict(row)
                    if config['metadata']:
                        try:
                            config['metadata'] = json.loads(config['metadata'])
                        except:
                            config['metadata'] = {}
                    return config
                
                return None
                
        except Exception as e:
            logger.error(f"Failed to get XML config by ID {config_id}: {e}")
            return None
    
    def get_config_by_path(self, file_path: str) -> Optional[Dict[str, Any]]:
        """Get XML configuration by file path."""
        try:
            file_path = str(Path(file_path).resolve())
            
            with sqlite3.connect(self.db_path) as conn:
                conn.row_factory = sqlite3.Row
                cursor = conn.execute('''
                    SELECT * FROM xml_configs WHERE file_path = ?
                ''', (file_path,))
                
                row = cursor.fetchone()
                if row:
                    config = dict(row)
                    if config['metadata']:
                        try:
                            config['metadata'] = json.loads(config['metadata'])
                        except:
                            config['metadata'] = {}
                    return config
                
                return None
                
        except Exception as e:
            logger.error(f"Failed to get XML config by path {file_path}: {e}")
            return None
    
    def update_usage(self, config_id: int):
        """Update usage statistics for a configuration."""
        try:
            with sqlite3.connect(self.db_path) as conn:
                conn.execute('''
                    UPDATE xml_configs 
                    SET last_used = CURRENT_TIMESTAMP, use_count = use_count + 1
                    WHERE id = ?
                ''', (config_id,))
                
        except Exception as e:
            logger.error(f"Failed to update usage for config {config_id}: {e}")
    
    def delete_config(self, config_id: int) -> bool:
        """Delete XML configuration by ID."""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.execute('DELETE FROM xml_configs WHERE id = ?', (config_id,))
                if cursor.rowcount > 0:
                    logger.info(f"Deleted XML config ID: {config_id}")
                    return True
                
                return False
                
        except Exception as e:
            logger.error(f"Failed to delete XML config {config_id}: {e}")
            return False
    
    def scan_directory(self, directory: str, recursive: bool = True) -> int:
        """Scan directory for XML files and add them to database."""
        try:
            directory = Path(directory)
            if not directory.exists():
                raise FileNotFoundError(f"Directory not found: {directory}")
            
            pattern = "**/*.xml" if recursive else "*.xml"
            xml_files = list(directory.glob(pattern))
            
            added_count = 0
            for xml_file in xml_files:
                try:
                    # Check if already in database
                    existing = self.get_config_by_path(str(xml_file))
                    if not existing:
                        self.add_xml_config(str(xml_file))
                        added_count += 1
                    else:
                        logger.debug(f"XML config already exists: {xml_file}")
                        
                except Exception as e:
                    logger.warning(f"Failed to add XML config {xml_file}: {e}")
            
            logger.info(f"Scanned {directory}, added {added_count} new XML configs")
            return added_count
            
        except Exception as e:
            logger.error(f"Failed to scan directory {directory}: {e}")
            return 0
    
    def get_statistics(self) -> Dict[str, Any]:
        """Get database statistics."""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.execute('SELECT COUNT(*) as total FROM xml_configs')
                total = cursor.fetchone()[0]
                
                cursor = conn.execute('''
                    SELECT COUNT(*) as used FROM xml_configs WHERE use_count > 0
                ''')
                used = cursor.fetchone()[0]
                
                cursor = conn.execute('''
                    SELECT name, use_count FROM xml_configs 
                    WHERE use_count > 0 ORDER BY use_count DESC LIMIT 5
                ''')
                most_used = cursor.fetchall()
                
                return {
                    'total_configs': total,
                    'used_configs': used,
                    'unused_configs': total - used,
                    'most_used': [{'name': row[0], 'count': row[1]} for row in most_used]
                }
                
        except Exception as e:
            logger.error(f"Failed to get statistics: {e}")
            return {}


class XMLConfigManager:
    """High-level manager for XML configurations."""
    
    def __init__(self, db_path: Optional[str] = None):
        """Initialize XML config manager."""
        self.db = XMLConfigDatabase(db_path)
    
    def register_xml(self, file_path: str, name: Optional[str] = None, 
                    description: Optional[str] = None) -> int:
        """Register an XML configuration file."""
        return self.db.add_xml_config(file_path, name, description)
    
    def list_xmls(self, limit: int = 20, search: Optional[str] = None) -> List[Dict[str, Any]]:
        """List registered XML configurations."""
        return self.db.list_configs(limit, search)
    
    def run_xml_by_id(self, config_id: int, extra_params: Optional[Dict[str, Any]] = None) -> Tuple[bool, str]:
        """Run XML configuration by ID."""
        from ..test.simple_runner import SimpleTestRunner
        import subprocess
        import os
        
        try:
            # Get config from database
            config = self.db.get_config_by_id(config_id)
            if not config:
                return False, f"XML config ID {config_id} not found"
            
            file_path = Path(config['file_path'])
            if not file_path.exists():
                return False, f"XML file not found: {file_path}"
            
            # Update usage statistics
            self.db.update_usage(config_id)
            
            # Parse XML to get execution parameters
            parser = XMLConfigParser()
            if not parser.load_from_file(str(file_path)):
                return False, f"Failed to parse XML: {parser.get_last_error()}"
            
            xml_config = parser.parse()
            
            # Extract execution parameters
            binary_section = xml_config.get_sublist("Binary")
            logging_section = xml_config.get_sublist("Logging")
            
            binary_path = binary_section.get_string("binary_path")
            work_dir = binary_section.get_string("work_dir", None)
            mpi_processes = binary_section.get_int("mpi_processes", 1)
            arguments = binary_section.get_string("arguments", "")
            log_file = logging_section.get_string("log_file") if logging_section else None
            
            # Resolve binary path relative to work_dir if needed
            if binary_path and not Path(binary_path).is_absolute() and work_dir:
                binary_path = str(Path(work_dir) / binary_path)
            
            if not binary_path or not Path(binary_path).exists():
                return False, f"Binary not found: {binary_path}"
            
            # Prepare command
            cmd = []
            
            # Change to work directory if specified
            original_cwd = None
            if work_dir and Path(work_dir).exists():
                original_cwd = os.getcwd()
                os.chdir(work_dir)
            
            try:
                # Build execution command
                if mpi_processes > 1:
                    cmd = ["mpirun", "-np", str(mpi_processes), binary_path]
                else:
                    cmd = [binary_path]
                
                # Add config parameter for non-system binaries
                binary_name = Path(binary_path).name.lower()
                if binary_name not in ['date', 'echo', 'ls', 'cat', 'head', 'tail', 'grep', 'julia']:
                    cmd.append(f"--config={file_path}")
                
                # Add any extra arguments from XML
                if arguments:
                    cmd.extend(arguments.split())
                
                # Execute command
                if log_file:
                    # Resolve log file path
                    log_path = Path(log_file)
                    if not log_path.is_absolute() and work_dir:
                        log_path = Path(work_dir) / log_file
                    
                    # Ensure log directory exists
                    log_path.parent.mkdir(parents=True, exist_ok=True)
                    
                    # Run with logging
                    with open(log_path, 'w') as f:
                        result = subprocess.run(cmd, stdout=f, stderr=subprocess.STDOUT, text=True)
                    
                    output = f"Command: {' '.join(cmd)}\nLog file: {log_path}\nExit code: {result.returncode}"
                else:
                    # Run without logging
                    result = subprocess.run(cmd, capture_output=True, text=True)
                    output = f"Command: {' '.join(cmd)}\nExit code: {result.returncode}\n\nSTDOUT:\n{result.stdout}\n\nSTDERR:\n{result.stderr}"
                
                success = result.returncode == 0
                
                # Store result in main database
                try:
                    from ..database.simple_db import SimpleDatabase
                    from ..core.simple_config import get_config
                    
                    main_config = get_config()
                    main_db = SimpleDatabase(main_config.get('database.path'))
                    
                    run_id = main_db.add_test(
                        solver=Path(binary_path).stem,
                        gpu=None,
                        params={
                            'xml_config_id': config_id,
                            'xml_file': str(file_path),
                            'binary_path': binary_path,
                            'work_dir': work_dir,
                            'mpi_processes': mpi_processes
                        },
                        log_content=output
                    )
                    
                    output += f"\n\nRun ID: {run_id}"
                    
                except Exception as db_error:
                    logger.warning(f"Failed to store run in main database: {db_error}")
                
                return success, output
                
            finally:
                # Restore original working directory
                if original_cwd:
                    os.chdir(original_cwd)
            
        except Exception as e:
            return False, f"Failed to run XML config: {e}"
    
    def auto_scan_configs(self) -> int:
        """Auto-scan common directories for XML configs."""
        scan_dirs = [
            Path.cwd() / "config",
            Path.cwd() / "configs",
            Path.cwd(),
            Path.home() / ".gsm" / "configs"
        ]
        
        total_added = 0
        for scan_dir in scan_dirs:
            if scan_dir.exists():
                added = self.db.scan_directory(str(scan_dir), recursive=True)
                total_added += added
        
        return total_added
    
    def get_config_info(self, config_id: int) -> Optional[Dict[str, Any]]:
        """Get detailed information about a configuration."""
        return self.db.get_config_by_id(config_id)
    
    def delete_xml(self, config_id: int) -> bool:
        """Delete XML configuration."""
        return self.db.delete_config(config_id)
    
    def update_xml(self, config_id: int, **updates) -> bool:
        """Update specific fields of an XML configuration."""
        try:
            with sqlite3.connect(self.db.db_path) as conn:
                # Build update query dynamically
                valid_fields = ['name', 'description', 'binary_path', 'mpi_processes', 'log_file', 'work_dir']
                update_fields = []
                update_values = []
                
                for field, value in updates.items():
                    if field in valid_fields:
                        update_fields.append(f"{field} = ?")
                        update_values.append(value)
                
                if not update_fields:
                    return False
                
                update_values.append(config_id)
                query = f"UPDATE xml_configs SET {', '.join(update_fields)} WHERE id = ?"
                
                cursor = conn.execute(query, update_values)
                return cursor.rowcount > 0
                
        except Exception as e:
            logger.error(f"Failed to update XML config {config_id}: {e}")
            return False
    
    def get_stats(self) -> Dict[str, Any]:
        """Get XML configuration statistics."""
        return self.db.get_statistics()


# Global instance
_xml_manager = None

def get_xml_manager() -> XMLConfigManager:
    """Get global XML configuration manager."""
    global _xml_manager
    if _xml_manager is None:
        _xml_manager = XMLConfigManager()
    return _xml_manager